package com.sl.au.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Grading;
import com.sl.au.entity.GridAreaInfo;
import com.sl.au.entity.GridUserDetails;
import com.sl.au.entity.Template;
import com.sl.au.entity.TemplateDetails;
import com.sl.au.entity.User;

import java.util.List;

public interface GridUserDetailsRepository extends JpaRepository<GridUserDetails, String> {
	
	@Query("select distinct a.grading from GridUserDetails a where a.grid.id = ?1")
	List<Grading> getGradingByGrid(String gridId);
	
	@Query("select a from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2 and a.user is null")
	GridUserDetails getByGridAndGradingAndNotUser(String gridId, String gradingId);
	
	@Query("select a from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2")
	List<GridUserDetails> getByGridAndGrading(String gridId, String gradingId);
	
	@Query("select a from GridUserDetails a where a.grid.id = ?1 and a.user is not null")
	List<GridUserDetails> getByGrid(String gridId);
	
	@Query("select a from GridUserDetails a where a.grid.id = ?1 order by a.grading.gradingLevel.extended1 asc")
	List<GridUserDetails> getAllByGrid(String gridId);
	
	@Modifying
	@Transactional
	@Query("delete from GridUserDetails a where a.grid.id = ?1")
	int deleteByGrid(String gridId);

	@Modifying
	@Transactional
	@Query("delete from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2")
	int deleteByGridAndGrading(String gridId, String gradingId);
	
	@Modifying
	@Transactional
	@Query("delete from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2 and a.user.id = ?3")
	int deleteGridUserDetailsByUser(String gridId, String gradingId, String userId);
	
	@Modifying
	@Transactional
	@Query("delete from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2 and a.user.id in (?3)")
	int deleteAllGridUserDetailsByUsers(String gridId, String gradingId, String[] users);
	
	@Query("select count(a) from GridUserDetails a where a.grading.id = ?1")
	int getGradingNum(String gradingId);
	
	/************** 塔型分析 *************/
	
	@Query("select distinct a.user from GridUserDetails a where a.grid.id = ?1 and a.grading.id = ?2 and a.user is not null")
	List<User> getUserByGridAndGrading(String gridId, String gradingId);
}
